using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using Common.Utilities;

namespace Workflows.Components.Reports
{
    public class DateFieldAggregate : AggregateBase
    {
        private DateTime _Min;
        private DateTime _Max;
        private DateTime _Median;
        private DateTime _Mode;
        private Dictionary<Range<DateTime>, int> _Spread;       // day_number vs. count 
        private DateTime _StartDate = new DateTime(1900, 1, 1);

        public DateFieldAggregate(string connStr, string tblName, string colName)
            : base(connStr, tblName, colName)
        {
            this.SummarizeData(connStr, tblName, colName);
        }

        private void SummarizeData(string connStr, string tblName, string colName)
        {
            if (this.Type != AggregateDataType.Time)
            {
                throw new Exception("This class only supports date type");
            }
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select min ([" + colName + "]) as DateFrom, " +
                    "max ([" + colName + "]) as DateTo from [" +
                    tblName + "]";
                SqlDataReader reader = cmd.ExecuteReader();
                this._Min = DateTime.MinValue;
                this._Max = DateTime.MaxValue;
                if (reader.Read())
                {
                    this._Min = reader.GetDateTime(0);
                    this._Max = reader.GetDateTime(1);
                }
                reader.Close();
                if (this.DistinctValueCount > 0)
                {
                    cmd.CommandText = "select [" + colName + "] from [" +
                        tblName + "] where [" + colName + "] is not null " +
                        "order by [" + colName + "]";
                    reader = cmd.ExecuteReader();
                    int index = 0;
                    DateTime dateTimeValue=this._Min;
                    while (reader.Read() && index < this.Count / 2)
                    {
                        dateTimeValue = DateTime.Parse(reader[0].ToString());
                        index++;
                    }
                    reader.Close();
                    this._Median = dateTimeValue;
                    cmd.CommandText = "select top 1 [" + colName + "] from [" +
                        tblName + "] where [" + colName + "] is not null " +
                        "group by [" + colName + "] order by count ([" +
                        colName + "]) desc";
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        this._Mode = DateTime.Parse(reader[0].ToString());
                    }
                    reader.Close();
                }
                else
                {
                    this._Mode = this._Min;
                    this._Median = this._Min;
                }

                this._Spread = new Dictionary<Range<DateTime>, int>();
                if (this._Min > DateTime.MinValue && this._Max < DateTime.MaxValue && this._Max > this._Min)
                {
                    TimeSpan interval = new TimeSpan((this._Max.Ticks - this._Min.Ticks) / 10);
                    for (int i = 0; i < 10; i++)
                    {
                        DateTime dateFrom = this._Min.Add(new TimeSpan(interval.Ticks * i));
                        DateTime dateTo = dateFrom.Add(interval);
                        Range<DateTime> range = new Range<DateTime>(dateFrom, dateTo);
                        cmd.CommandText = "select count (*) from [" + tblName + "] " +
                            "where [" + colName + "]>='" + dateFrom.ToString() + "' and [" +
                            colName + "]<='" + dateTo.ToString() + "'";
                        reader = cmd.ExecuteReader();
                        int count = 0;
                        if (reader.Read())
                        {
                            try
                            {
                                count = reader.GetInt32(0);
                            }
                            catch
                            {
                                count = 0;
                            }
                        }
                        reader.Close();
                        this._Spread.Add(range, count);
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        #region properties
        [Browsable(true), Category("Stats")]
        public Dictionary<Range<DateTime>, int> Spread
        {
            get
            {
                return this._Spread;
            }
        }
        [Browsable(true), Category("Stats")]
        public DateTime Max
        {
            get { return _Max; }
            set { _Max = value; }
        }
        [Browsable(true), Category("Stats")]
        public DateTime Min
        {
            get { return _Min; }
            set { _Min = value; }
        }
        [Browsable(true), Category("Stats")]
        public DateTime Median
        {
            get
            {
                return this._Median;
            }
        }
        [Browsable(true), Category("Stats")]
        public new DateTime Mode
        {
            get
            {
                return this._Mode;
            }
        }
        [Browsable(true), Category("Stats")]
        public DateTime Mean
        {
            get
            {
                return DateTime.MinValue;
            }
        }
        #endregion
    }
}
